Table Builder

Introduction

Custom tables are used to create independent database tables for persistent data storage. Tables are stored in the Resolve Actions Pro database and are always accessible to ActionTask Assessors and Runbooks. In addition to storing information in third-party applications:

• Actions Pro workflow and event-based state modeling allow building custom processes and applications previously limited by traditional domain-specific applications.
• A custom table can be created to store uploaded files accessible via the database.

Each custom table has views options:

• List (Grid): displays all the tables in the database created by the user and allow the user to view or delete tables.
• Table Definition (Edit): shows name, display name, table column, and different custom table views, with a link to the associated custom form.

Creating Custom Tables

You have two options to create a custom table:

• With a form—When you use the Form Builder to create a form and set the table name to a new name, the Form Builder automatically creates a DB table based on the form fields that you set as DB type.
• With a CSV import—You can create a table without a form attached to it using a CSV import. Keep in mind that attaching a form to a table created through a CSV import is not supported.

With a Form

Custom Form Builder works to create tables and forms. A custom form can either be a:

• Standalone form (with no table defined with it)
• Table-based form (with a custom table defined with the form)

When a custom table is created using the Form Builder, it creates an associated custom form. This form is used for data entry into the table.

To access the Custom Form Builder:

1. Locate the Table Administration > View Forms.
   A list of available forms displays.
2. Click Create in the control menu.
   A new and untitled form displays in a new browser window.
3. Under Form Settings, enter a unique Table Name.
   This will be the table name in the database.
   note

   Avoid using spaces and use the underscore (_) character in Table Name.

4. Drag-and-drop the form fields that you want to appear on the form onto the left side of the builder.
5. For fields that you want to populate from the DB, do the following:
   1. Select the field on the form.
   2. In the Field Settings tab on the right, set Type to DB.
   3. In Field Name, enter the name of the DB table column as you want it to appear in the DB.
      The DB column is created automatically. The field name must start with the u_ prefix. It is added automatically if you don't add it explicitly.
6. Add a database Submit button to the form.
   This is the only method that allows data submission to the table.
   1. Select the Forms Settings tab.
   2. Locate the Buttons heading and click on the Add pick-list and select the Button option.
   3. Double click the default Name (New Button) and select the box to allow changes. Rename the button.
   4. Select the button and click Add Action. Chose these settings:
      • Operation: DB
      • DB Action: Submit.
   5. Select Save.
      When the form is saved it creates the custom table and the added fields become table columns.
7. After the custom form and corresponding table are saved, the new custom table can be viewed in the "Custom Tables List" from Table Administration >View Tables.
8. Use the Custom Form Viewer to enter data into the database table.
9. Locate the Table Administration > Form Views. When you click Form Views, a list of available forms display.
10. Select a form.
11. Click on View.
12. Add the information to any one of the available fields.
13. Select Submit to enter the new information.

With CSV Import

You might want to create an empty table without a form or to import data from another system as a custom table. For those cases, you have the option to create a custom table through a CSV file import. During the import, you can choose from the following options:

• Create an empty table with the column structure defined in the CSV.
• Create the table and import the data, if any.

note

Attaching a form to a table created through a CSV import is not supported.

CSV Format Notes

When preparing or exporting your CSV file, keep the following in mind:

• Only comma (,) is supported as the field delimiter
• UTF-8 encoding is not supported

Take these steps to create a custom DB table using a CSV import:

1. In the Main Menu, click Table Administration > Create Table.
2. Click New.
3. In Table Name, enter a table name that conforms to your DB vendor's naming conventions.
4. In Data CSV, click Browse and then select the CSV file from your computer.
5. In Populate Table, choose one of the options:
   • Toggle it to the left to only create an empty DB table with the column structure defined in the CSV.
   • Toggle it to the right to create the DB table and import the data from he CSV.
6. Under Include Column, do the following:
   • Use the check boxes to select the columns that you want to import.
   • Use the Column Type pickers to select the data type of each column.
7. Click Import Data.
8. Click Complete.

note

If a CSV record contains data mismatches, it is not imported. Actions Pro will silently tolerate such records up to a certain percent of the total record number, after which it will abort the import. You can set the threshold percentage using the rsview.csvimport.invalidrecords.threshold system property (Main Menu > System Administration > System Properties).

You can see how many records were imported and how many were rejected in the Import Logs.

note

An alternative to using the Create Table menu item is to create an Automation Page and add the CSV Import component to it. The component has the same functionality as the Create Table menu item.

Viewing Import Logs

You can view information about the CSV import operation after it has completed.

Take these steps to view the log of a particular CSV import:

1. In the Main Menu, click Table Administration > CSV Import Logs.
2. In the table, click the name of the table that was created with the CSV import.

The import log appears on the screen.

Using Existing Custom Tables

You can base a new form on an existing custom table. Tables created using a CSV import, however, cannot be used with a form.

Use these steps to modify an existing custom table.

1. Create a new Custom Form.
2. Select Form Settings.
3. Select an existing Table Name from the pick list.
   This is also the Table Name of an existing table in the database.
4. Add any field by clicking on any of the different selections. When you select a field, the field displays on the right-hand side of the window.
5. Click Field Settings.
   The "Type" option must be set to DB (for database).
6. Use Field Name pick list to link the added field, to existing field, for the selected table. The grayed out database fields do not match selected from fields types.
7. Save the form. This will link the field to the existing Custom Table.
8. After the Save the added fields will be linked to the existing table column. The existing custom table can be viewed in the "Custom Tables List". Select Table Administration > View Tables.
9. Use Custom Form Viewer to submit data into the database table.

Editing Custom Tables

How you can edit a custom table depends on how it was created in the first place:

• For tables created through a form, you can add columns by editing the form.
• For tables created through a CSV import, you can update the table through a new CSV import.

Editing Form Tables

When you add a DB-type field to a form that connects to the custom table that you want to edit, the field is added to the table as a new column. See Form Builder for details.

When removing fields from a form that connects to the custom table, the corresponding table column remains in the custom table. This way, other forms using the table will not be affected.

Editing CSV-imported Tables

The editing actions that you can take with a custom table originally created through a CSV imports are as follows:

• You can replace the existing table rows with new rows, keeping the column structure and types.
• You can append rows to the existing table rows, keeping the column structure and types.

All actions require you to prepare a new CSV import.

Any columns that exist in the update CSV but are missing from the custom table are ignored.

Any CSV lines that contains data not matching the custom table's column types will be ignored.

Take these steps to edit a CSV-imported custom table:

1. In the Main Menu, click Table Administration > Create Table.
2. In the list, click the table that you want to edit.
   The Update Table screen appears.
3. In Data CSV, click Browse and then select the updated CSV file from your computer.
4. Using Truncate Table, specify what to do with the existing table data in Actions Pro:
   • Enable it to delete the existing table data before importing the new CSV data.
   • Disable it to append the new CSV data to the existing table data.
     note

     Data deduplication is not performed. All valid CSV lines are appended as new table rows with unique system fields even if they repeat existing rows.

5. Click Import Data.
6. Click Complete.

note

If a CSV record contains data mismatches, it is not imported. Actions Pro will silently tolerate such records up to a certain percent of the total record number, after which it will abort the import. You can set the threshold percentage using the rsview.csvimport.invalidrecords.threshold system property (Main Menu > System Administration > System Properties).

You can see how many records were imported and how many were rejected in the Import Logs.

Managing Table Views

Custom tables created as a backend to a form can have views attached to them. A view is a collection of a create forma and an edit form. You can also control the access to the view using roles.

note

Custom tables created through CSV import don't have views as forms cannot be attached to them.

Take these steps to manage views:

1. In the Main Menu, click Table Administration > View Tables.
2. On the table's row, click the View Details icon at the beginning of the row.
3. In the table properties page that opens, use the Views section to manage views:

• New - opens a Custom Table View template used to create a new table view.
• Delete - deletes existing views.
• View – displays the existing table view with meta-data.
• Click an existing view to edit it.

When editing or creating a view, the Create Form and Edit Form fields allow you to select what forms to use for creating records and editing records.

The Advanced Configuration option allows for backward compatibility with custom forms created in previous Actions Pro versions:

1. Select the Checkbox.
2. Enter a customized URL in create and edit URL fields.
3. Save.

Viewing Custom Tables

A Custom Table View shows the custom table, meta-data, in the data columns.

• Each column can be displayed or hidden
• Access Rights, using roles, can be attached to each custom table view
• Specific, frequently-used, table views can be labeled and saved

Permissions to custom tables are added at the viewing level and control what can be viewed. Specifically formatted custom table views can be used to secure access to table information and determine what subset of data is seen by (or hidden from) particular viewers.

When a custom table is first created, a "Default" custom table view is also created that displays all columns, including system information columns (sys_id, created/updated users and timestamps).

In the "Default" view, only those with "admin" roles have access rights, with full admin, edit, and view permissions. A developer with "admin" rights can create custom tables using the Form Builder.

Frequently-used custom table views with user-configured filters can be named and saved. Saved views available to use for a given table are listed in the "Views" panel of the Custom Table Edit screen.

The data stored in a custom table cannot be edited through the "Custom Table View". The custom form viewer must be used to enter or delete.

• To view a custom table, select the table from the list and press the view button.
• The custom table is displayed with all the data records listed. It is formatted according to the viewer's current access rights.

The table below shows the commands available on the custom tables view page.

Name	Function
NewEdit	These command buttons open Create URL and Edit URL specified in Custom Table View
Refresh	Update the window to show the current available files and information. The refresh rate is adjustable.
Previous/Next (Page)	Scroll back or forward to previous/next page of posts. Actions Pro is limited to showing 50 records per page. This feature is useful if the table contains multiple data records.
Select All	Highlights and selects all the data rows displayed for deleting.
Filter Pulldown	This pull-down feature filters out displayed data either using Advance Search or "double-click to search" as shown below. The Save button records any new filter configuration created. Save View As – Used to save the current filter as the default view or give it a unique name for later use. Manage Filter – This option is available at the bottom of the available filter list. This feature is used only to delete existing filters. See the last entry in the pulldown above and select Manage Filters.
Custom Table Views Pulldown	Views – Displays the Table Default View. The view can be changed by selecting any other view that is available. Settings – changes Table Definition view. Used to create more or add new table views. Save view as – use Advance Search or "double-click search", filter out data. This feature saves the settings as the default view or allows a unique name to be assigned for later use.
Advanced Search	Allows preferences to be readily set for every column displayed in the table.
View	Allows you to see the form and access its features.
Delete	Removes the selected data row from the table.

Deleting Custom Tables

To delete a custom table:

1. Locate the Table Administration > View Forms. When you click View Forms, a list of available forms displays.
2. Select the table.
3. Press the Delete button at the top of the list. Deleting a custom table:
   • Removes the associated custom form and all the fields in other custom tables that reference it.
   • It also deletes File Upload Table when you use the File Manager feature from Available Fields.